--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_fdv_percomm AS (SELECT * FROM &erp.fdv_percomm WHERE 1=2);



DELETE FROM se_fdv_percomm;


 INSERT INTO se_fdv_percomm
 (SELECT id percomm_cod,
         0 enreg_id,
         regroup percommregr_cod,
         NULL percommcateg_cod,
         'R' percommtype_cod,
         NULL commprf_cod,
         0 adresse_id,
         NULL devise_cod,
         NULL commcalcprf_cod,
         secteur sectcial_cod,
         'N' enreg_s,
         NOM percomm_lib,
         NULL percomm_lib2
    FROM UV_CREPRESENTANTS) ;




 UPDATE se_fdv_percomm tf SET (enreg_id,adresse_id)=(select enreg_id,adresse_id FROM &erp.fdv_percomm f WHERE f.PERCOMM_COD=tf.PERCOMM_COD) ;
 UPDATE se_fdv_percomm tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;

 UPDATE se_fdv_percomm SET adresse_id=&outils.gen_adresse.NEXTVAL WHERE adresse_id IS NULL OR adresse_id =0;





DELETE FROM  se_gen_adresse ;

INSERT INTO se_gen_adresse
 (SELECT (SELECT adresse_id FROM se_fdv_percomm s WHERE s.percomm_cod=fuv.ID) adresse_id,
    NULL strucjur_cod,
    null traduction_cod,
    NULL expedzone_cod,
    0 enreg_id,
    null pays_cod,
    null departement_cod,
    NULL region_cod,
    'M.' prefnom_cod,
    nom nom,
    null nom2,
    rue adr1,
    bd adr2,
    NULL adr3,
    NULL bp,
    cp cpostal_cod,
    ville ville_cod,
    tel tel1,
    NULL tel2,
    fax fax1,
    NULL fax2,
    null telmobile1,
    NULL telmobile2,
    email email,
    NULL siteweb,
    NULL contact_nom,
    NULL tvacee_no,
    NULL siret_no,
    NULL ape_no,
    NULL gln_no,
    NULL cnuf_no,
    NULL rc_no,
    NULL villerc_lib,
    NULL capital,
    NULL logoachat_lib,
    NULL creatsoc_dat,
    NULL envoi_typ,
    NULL villeinsee_cod,
    NULL ediadr_no

    FROM UV_CREPRESENTANTS   fuv
      ) ;

 UPDATE se_gen_adresse tf

 SET enreg_id = (select enreg_id FROM &erp.gen_adresse f WHERE f.adresse_id=tf.adresse_id )
 WHERE EXISTS (SELECT 1 FROM &erp.gen_adresse f WHERE f.adresse_id=tf.adresse_id);

 UPDATE se_gen_adresse SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;

 CALL uvse_synchroverserp('adresse_id','GEN_ADRESSE','O') ;


 CALL uvse_synchroverserp('percomm_cod','FDV_PERCOMM','O') ;

 commit;


select * from (
SELECT &outils.stragg(source) source,percomm_cod, enreg_id, percommregr_cod, percommcateg_cod, percommtype_cod, commprf_cod, adresse_id, devise_cod, commcalcprf_cod, sectcial_cod, enreg_s, percomm_lib, percomm_lib2
FROM (
  SELECT 'uv' source ,g.* FROM se_fdv_percomm g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.fdv_percomm g
  )
GROUP BY percomm_cod, enreg_id, percommregr_cod, percommcateg_cod, percommtype_cod, commprf_cod, adresse_id, devise_cod, commcalcprf_cod, sectcial_cod, enreg_s, percomm_lib, percomm_lib2
HAVING Count(1)!=2) r
ORDER BY percomm_cod;

 SELECT &outils.stragg(source) source,adresse_id, strucjur_cod, traduction_cod, expedzone_cod, enreg_id, pays_cod, departement_cod, region_cod, prefnom_cod, nom, nom2, adr1, adr2, adr3, bp, cpostal_cod, ville_cod, tel1, tel2, fax1, fax2, telmobile1, telmobile2, email, siteweb, contact_nom, tvacee_no, siret_no, ape_no, gln_no, cnuf_no, rc_no, villerc_lib, capital, logoachat_lib, creatsoc_dat, envoi_typ, villeinsee_cod, ediadr_no
FROM (
  SELECT 'uv' source ,g.* FROM se_gen_adresse g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.gen_adresse g WHERE EXISTS (SELECT * FROM se_fdv_percomm a WHERE a.adresse_id=g.adresse_id)
  )
GROUP BY adresse_id, strucjur_cod, traduction_cod, expedzone_cod, enreg_id, pays_cod, departement_cod, region_cod, prefnom_cod, nom, nom2, adr1, adr2, adr3, bp, cpostal_cod, ville_cod, tel1, tel2, fax1, fax2, telmobile1, telmobile2, email, siteweb, contact_nom, tvacee_no, siret_no, ape_no, gln_no, cnuf_no, rc_no, villerc_lib, capital, logoachat_lib, creatsoc_dat, envoi_typ, villeinsee_cod, ediadr_no
HAVING Count(1)!=2
ORDER BY adresse_id;


